<?php
namespace App\Repositories;


use App\Models\Coach;
use App\Models\Comment;
use App\Models\PutPeriod;
use App\Models\Reply;
use App\Models\School;
use App\Models\Student;
use App\Models\StudentCoach;
use App\Models\Subscribe;
use App\Models\TcpStudytime;
use App\Models\User;
use Carbon\Carbon;
use Illuminate\Support\Facades\DB;

class CommentsRepository extends BaseRepository
{
    public $error = '';
    public function __construct(
        Comment $comment
    )
    {
        $this->model = $comment;
    }

    /**
     * 资源列表
     * @param  array $param 必须传入与模型查询相关的数据
     * @param  string|array $extra 可选额外传入的参数
     * @param  string $size 分页大小（存在默认值）
     * @return Illuminate\Support\Collection
     */
    public function index($param=[], $extra='', $size='10')
    {
        $comments = $this->model->where('student_id', $extra->id)->limit($size)->offset(($param['page'] - 1) * $size)->orderBy('id', 'desc')->get();
        $count = $this->model->where('student_id', $extra->id)->count();

        if($comments){

            foreach ($comments as $comment) {
                $comment->putime = [];
                $subscribe_info = Subscribe::leftJoin('yy_put_periods','yy_put_periods.id','=','yy_subscribes.putime_id')
                                            ->leftJoin('coachs','coachs.id','=','yy_subscribes.coach_id')
                                            ->select('yy_put_periods.km','yy_put_periods.startime as starttime','yy_put_periods.endtime','coachs.name as coach','coachs.coachnum','yy_put_periods.sum_period as minute')
                                            ->where('yy_subscribes.id', $comment->cid)
                                            ->first();
                if($subscribe_info){
                    $comment->putime = [$subscribe_info];
                }

            }
        }


        return ['list' => $comments, 'count' => $count];

    }



    /**
     * 评价统计
     * @param $coach_id
     * @return array
     */
    public function statistics($student_id)
    {
        $data = [
            "km1_very_good" => $this->model->leftJoin('tcp_studytimes as ts', 'ts.id', '=', 'comments.cid')
                                                ->where('ts.km', 1)
                                                ->where('comment',2)
                                                ->where('student_id',$student_id)->count(),
            "km2_very_good" => $this->model->leftJoin('tcp_studytimes as ts', 'ts.id', '=', 'comments.cid')
                                                ->where('ts.km', 2)
                                                ->where('comment',2)
                                                ->where('student_id',$student_id)->count(),
            "km3_very_good" => $this->model->leftJoin('tcp_studytimes as ts', 'ts.id', '=', 'comments.cid')
                                                ->where('ts.km',3)
                                                ->where('comment',2)
                                                ->where('student_id',$student_id)->count(),
            "km4_very_good" => $this->model->leftJoin('tcp_studytimes as ts', 'ts.id', '=', 'comments.cid')
                                                ->where('ts.km',4)
                                                ->where('comment',2)
                                                ->where('student_id',$student_id)->count(),
            "km1_good" => $this->model->leftJoin('tcp_studytimes as ts', 'ts.id', '=', 'comments.cid')
                                                ->where('ts.km', 1)
                                                ->where('comment',1)
                                                ->where('student_id',$student_id)->count(),
            "km2_good" => $this->model->leftJoin('tcp_studytimes as ts', 'ts.id', '=', 'comments.cid')
                                                ->where('ts.km', 2)
                                                ->where('comment',1)
                                                ->where('student_id',$student_id)->count(),
            "km3_good" => $this->model->leftJoin('tcp_studytimes as ts', 'ts.id', '=', 'comments.cid')
                                                ->where('ts.km',3)
                                                ->where('comment',1)
                                                ->where('student_id',$student_id)->count(),
            "km4_good" => $this->model->leftJoin('tcp_studytimes as ts', 'ts.id', '=', 'comments.cid')
                                                ->where('ts.km',4)
                                                ->where('comment',1)
                                                ->where('student_id',$student_id)->count(),
            "km1_no_good" => $this->model->leftJoin('tcp_studytimes as ts', 'ts.id', '=', 'comments.cid')
                                                ->where('ts.km', 1)
                                                ->where('comment',0)
                                                ->where('student_id',$student_id)->count(),
            "km2_no_good" =>  $this->model->leftJoin('tcp_studytimes as ts', 'ts.id', '=', 'comments.cid')
                                                ->where('ts.km', 2)
                                                ->where('comment',0)
                                                ->where('student_id',$student_id)->count(),
            "km3_no_good" => $this->model->leftJoin('tcp_studytimes as ts', 'ts.id', '=', 'comments.cid')
                                                ->where('ts.km',3)
                                                ->where('comment',0)
                                                ->where('student_id',$student_id)->count(),
            "km4_no_good" => $this->model->leftJoin('tcp_studytimes as ts', 'ts.id', '=', 'comments.cid')
                                                ->where('ts.km',4)
                                                ->where('comment',0)
                                                ->where('student_id',$student_id)->count(),
            "sum" => $this->model->where('student_id',$student_id)->count(),
        ];

        $data["satisfaction"] = $data['sum'] ? 100-round(((int)$data['km2_no_good'] + $data['km3_no_good'])*100.0/(int)$data['sum']) : 0;
        return $data;
    }



    /**
     * 存储odb
     * @param $inputs
     */
    public function store($inputs, $extra = '')
    {

    }

    /**
     * 更新
     * @param $id
     * @param $input
     * @return mixed
     */
    public function update($id, $input,$extra = '')
    {
        $model = $this->getById($id);
        return $this->save($model,$input);
    }


    /**
     * 编辑特定id资源
     * @param  int $id 资源id
     * @param  string|array $extra 可选额外传入的参数
     * @return Illuminate\Support\Collection
     */
    public function edit($id, $extra)
    {

    }

    /**
     * @param $model
     * @param $inputs
     * @param bool $falg
     * @return mixed
     */
    public function save($model , $inputs , $falg = false)
    {
        return $model;
    }



    /**
    * 教练评论列表
    */
    public function coachComments($coach_id, $student, $type, $page, $size)
    {



        $query =  $this->model->leftJoin('students', 'students.id', '=', 'comments.student_id')
                            ->select('comments.*','students.name','students.photo')
                            ->whereIn('comments.coach_id', $coach_id)
                            ->where('student_id', $student->id);

        //非常满意
        if($type == 1){
            $query->where('comment', 2);
            //满意
        }elseif($type == 2){
            $query->whereBetween('comment', 1);
            //一般
        }elseif($type == 3){
            $query->where('comment', 0);
        }

        return $query->limit($size)->offset(($page - 1) * $size)->orderBy('id', 'desc')->get();
    }


    /**
     * 教练查看评价
     */
    public function serachComments($coach_info, $params, $page = 1, $size = 15)
    {

        $query =  $this->model->leftJoin('students', 'students.id', '=', 'comments.student_id')
                            ->leftJoin('tcp_studytimes', 'tcp_studytimes.id', '=', 'comments.cid')
                            ->select('comments.*','students.name','students.photo','tcp_studytimes.km','tcp_studytimes.starttime','tcp_studytimes.endtime')
                            ->where('comments.coach_id', $coach_info->id);

        //学员搜索
        if($params['keyword'] != ''){
            $query->where('students.name', 'like', '%'.$params['keyword'].'%');
        }

        //时间搜索
        if($params['starttime'] != '' && $params['endtime'] != ''){
            $query->where('createtime', '>=',strtotime($params['starttime']))->where('createtime', '<=', strtotime($params['endtime'].' +1 day') - 1);
        }elseif($params['starttime'] != ''){
            $query->where('createtime', '>=', strtotime($params['starttime']));
        }elseif($params['endtime'] != ''){
            $query->where('createtime', '<=', strtotime($params['endtime'].' +1 day') - 1);
        }

        $total = $query->count();
        $list = $query->limit($size)->offset(($page - 1) * $size)->orderBy('id', 'desc')->get();

        return [$total, $list];
    }

    public function getSchoolComment(School $school, $param, $page, $size)
    {
        $list = [];

        $count_info = $this->model->leftJoin('coachs', 'comments.coach_id', '=', 'coachs.id')
                            ->select(DB::raw('count(distinct(jx_comments.coach_id)) as coach_count, count(jx_comments.id) as comment_count'))
                            ->where('coachs.school_id', $school->id)->where('comments.type', 2)->first()->toArray();

        $good_comment = $this->model->leftJoin('coachs', 'comments.coach_id', '=', 'coachs.id')
                            ->where('coachs.school_id', $school->id)->where('comment','>=',1)->where('comments.type', 2)->count();

        $count_info['comment_percent'] = 0;
        if($count_info['comment_count']){
            $count_info['comment_percent'] = number_format($good_comment / $count_info['comment_count'], 2);
        }

        $list['count_info'] = $count_info;


        $list_sql = $this->model->leftJoin('students', 'students.id', '=', 'comments.student_id')
                            ->leftJoin('coachs', 'coachs.id', '=', 'comments.coach_id')
                            ->select('comments.*','students.name as stu_name','students.photo','coachs.name as coach_name')
                            ->where('coachs.school_id', $school->id)
                            ->where('comments.type', 2);
        if ($param['name']){
            $list_sql->where(function($sql) use ($param){
                $sql->where('students.name', 'like', '%'.$param['name'].'%');
                $sql->orWhere('coachs.name', 'like', '%'.$param['name'].'%');
            });
        } elseif ($param['start_time'] && $param['end_time']) {
            $list_sql->where('comments.createtime', '>=', strtotime($param['start_time']));
            $list_sql->where('comments.createtime', '<=', strtotime($param['end_time']));
        } elseif ($param['start_time']) {
            $list_sql->where('comments.createtime', '>=', strtotime($param['start_time']));
        } elseif ($param['end_time']) {
            $list_sql->where('comments.createtime', '<=', strtotime($param['end_time']));
        }

        if($param['type'] == 2){
            $list_sql->where('comments.comment', 0);
        }
        $list['count_info']['list_count'] = $list_sql->count();

        $comment_list = $list_sql->limit($size)->offset(($page - 1) * $size)
                            ->orderBy('comments.id','desc')->get();

        if($comment_list){

            foreach ($comment_list as $comment){

                $comment->startime = 0;
                $comment->endtime = 0;
                $comment->km = '';

                if($comment->type == 1){
                    $km_info = TcpStudytime::select('km','starttime as startime','endtime')->find($comment->cid);
                    if($km_info){
                        $comment->km = $km_info->km;
                        $comment->startime = strtotime($km_info->startime);
                        $comment->endtime = strtotime($km_info->endtime);
                    }
                } else {
                    $km_info = Subscribe::leftJoin('yy_put_periods', 'yy_put_periods.id', '=', 'yy_subscribes.putime_id')
                                        ->select('yy_put_periods.km','yy_put_periods.startime','yy_put_periods.endtime')
                                        ->find($comment->cid);
                    if($km_info){
                        $comment->km = $km_info->km;
                        $comment->startime = $km_info->startime;
                        $comment->endtime = $km_info->endtime;
                    }
                }

            }
        }

        $list['list'] = $comment_list;

        return $list;

    }

    /**
     * 驾校评论报表
     * @param $school
     */
    public function commentListReport($school)
    {
        $list['km2_perfect'] = $this->model->leftJoin(DB::raw('(select km,jx_yy_subscribes.id as sub_id,jx_yy_subscribes.school_id from jx_yy_subscribes left join jx_yy_put_periods on jx_yy_subscribes.putime_id = jx_yy_put_periods.id) as jx_ys'), 'ys.sub_id', '=', 'comments.cid')
        ->where('km',2)->where('school_id',$school->id)->where('comments.comment',2)->where('comments.type', 2)->count();

        $list['km2_good'] = $this->model->leftJoin(DB::raw('(select km,jx_yy_subscribes.id as sub_id,jx_yy_subscribes.school_id from jx_yy_subscribes left join jx_yy_put_periods on jx_yy_subscribes.putime_id = jx_yy_put_periods.id) as jx_ys'), 'ys.sub_id', '=', 'comments.cid')
            ->where('km',2)->where('school_id',$school->id)->where('comments.comment',1)->where('comments.type', 2)->count();

        $list['km2_bad'] = $this->model->leftJoin(DB::raw('(select km,jx_yy_subscribes.id as sub_id,jx_yy_subscribes.school_id from jx_yy_subscribes left join jx_yy_put_periods on jx_yy_subscribes.putime_id = jx_yy_put_periods.id) as jx_ys'), 'ys.sub_id', '=', 'comments.cid')
            ->where('km',2)->where('school_id',$school->id)->where('comments.comment',0)->where('comments.type', 2)->count();

        $list['km3_perfect'] = $this->model->leftJoin(DB::raw('(select km,jx_yy_subscribes.id as sub_id,jx_yy_subscribes.school_id from jx_yy_subscribes left join jx_yy_put_periods on jx_yy_subscribes.putime_id = jx_yy_put_periods.id) as jx_ys'), 'ys.sub_id', '=', 'comments.cid')
            ->where('km',3)->where('school_id',$school->id)->where('comments.comment',2)->where('comments.type', 2)->count();

        $list['km3_good'] = $this->model->leftJoin(DB::raw('(select km,jx_yy_subscribes.id as sub_id,jx_yy_subscribes.school_id from jx_yy_subscribes left join jx_yy_put_periods on jx_yy_subscribes.putime_id = jx_yy_put_periods.id) as jx_ys'), 'ys.sub_id', '=', 'comments.cid')
            ->where('km',3)->where('school_id',$school->id)->where('comments.comment',1)->where('comments.type', 2)->count();

        $list['km3_bad'] = $this->model->leftJoin(DB::raw('(select km,jx_yy_subscribes.id as sub_id,jx_yy_subscribes.school_id from jx_yy_subscribes left join jx_yy_put_periods on jx_yy_subscribes.putime_id = jx_yy_put_periods.id) as jx_ys'), 'ys.sub_id', '=', 'comments.cid')
            ->where('km',3)->where('school_id',$school->id)->where('comments.comment',0)->where('comments.type', 2)->count();

        $list['total_perfect']  = $this->model->leftJoin(DB::raw('(select km,jx_yy_subscribes.id as sub_id,jx_yy_subscribes.school_id from jx_yy_subscribes left join jx_yy_put_periods on jx_yy_subscribes.putime_id = jx_yy_put_periods.id) as jx_ys'), 'ys.sub_id', '=', 'comments.cid')
                        ->where('school_id',$school->id)->where('comments.comment',2)->where('comments.type', 2)->count();

        $list['total_good'] = $this->model->leftJoin(DB::raw('(select km,jx_yy_subscribes.id as sub_id,jx_yy_subscribes.school_id from jx_yy_subscribes left join jx_yy_put_periods on jx_yy_subscribes.putime_id = jx_yy_put_periods.id) as jx_ys'), 'ys.sub_id', '=', 'comments.cid')
            ->where('school_id',$school->id)->where('comments.comment',1)->where('comments.type', 2)->count();

        $list['total_bad'] = $this->model->leftJoin(DB::raw('(select km,jx_yy_subscribes.id as sub_id,jx_yy_subscribes.school_id from jx_yy_subscribes left join jx_yy_put_periods on jx_yy_subscribes.putime_id = jx_yy_put_periods.id) as jx_ys'), 'ys.sub_id', '=', 'comments.cid')
            ->where('school_id',$school->id)->where('comments.comment',0)->where('comments.type', 2)->count();


        return $list;
    }


    public function coachCommentReport(School $school)
    {
        $coach = Coach::select('name')->where('school_id',$school->id)->get();

        if($coach){

            foreach ($coach as $v){

                $v->perfect_comment = $this->model->where('coach_id', $v->id)->where('comment', 2)->count();
                $v->good_comment = $this->model->where('coach_id', $v->id)->where('comment', 1)->count();
                $v->bad_comment = $this->model->where('coach_id', $v->id)->where('comment', 0)->count();
            }
        }

        return $coach;
    }

}